IF OBJECT_ID('dbo.RptIndustryAgreementsReceived') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptIndustryAgreementsReceived
    IF OBJECT_ID('dbo.RptIndustryAgreementsReceived') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptIndustryAgreementsReceived >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptIndustryAgreementsReceived >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptIndustryAgreementsReceived
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 7/2/2007
--
-- Description: Source of Report, "IndustryAgreementReceivedReport.rdl"
--              In Insight DB Reporting Services
--
-- Parameters:  
--               @StartDate                  Project End Date
--               @EndDate                    
--               @Institution           
--               @SponsorType           -- Always INDUSTRY for Initial version
--               @ProposalStatus
--               @domainUserId         
--              
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptIndustryAgreementsReceived( )
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 7/2/07      KC        Created
-- 7/9/07      KC        Changed Naming Conventions. Finalized Rpt.
-- 7/18/07     KC        Added StartDate and EndDate as Params
--                       Added CSCR Manager Name
-- 7/23/07     KC        Fact table now has PropNumber on it.
-- 8/2/07      KC        Addressed QA Feedback, BudgetDate fix.
-- 8/7/2007    KC        Added '0' for 'ALL' in Params
-- 8/8/07      KC        Made Security Inner Join
-- 8/17/07     KC        Security changed to Cached table
-- 8/28/07     KC        Fixed JOIN on CSRCaseManager
--                       Fixed JOIN on InfoEdProposal, and changed 
--                       search date to ISNULL(PROP.create_dt,PROP.submt_dt)
-- 9/10/07     KC        Changed to new source tables
-- 9/25/07     KC        Used ID for Institution
-- 9/26/07     KC        Fixed problem with ProposalStatus
-- 9/28/07     KC        Added 'CSCR Case Manager Not Assigned'
-- 10/1/07     KC        Fixed outdated function reference. fnGetMultiCriteria
-- 10/18/07    KC        Fixed Searching on Dates with TimeComponent
-- 12/17/08    KC        New Security function
-- 01/05/08    KC        Security Function Perf Issues: use TempTable
--
-- =======================================================================
CREATE PROCEDURE dbo.RptIndustryAgreementsReceived
     (
          @StartDate                  DATETIME,      
          @EndDate                    DATETIME,
          @Institution                VARCHAR(100) = NULL,
          @ProposalStatus             VARCHAR(100) = NULL,
          @DomainUserId               INT
     )
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON

     -- Declare Local Variables
     DECLARE   @Error            INT,
               @RowCount         INT,
               @SponsorType      VARCHAR(10)
          

     -- ALL checking
     IF @Institution IN ('ALL','','0') SET @Institution = NULL
     IF @SponsorType IN ('ALL','','0') SET @SponsorType = NULL
     IF @ProposalStatus IN ('ALL','','0') SET @ProposalStatus = NULL
     SET  @SponsorType = 'INDUSTRY'

     -- Performance Issues, Get Secured AgreementIds into a temp table
     CREATE TABLE   #tt_Agreements  (AgreementId INT)
     
     INSERT    #tt_Agreements (AgreementId)
     SELECT    AgreementId
     FROM      dbo.fnGetSecurityAgreements(@DomainUserId) 

     -- Return Query Results
     SELECT    AI.PrincipalInvestigator AS PrincipalInvestigator,
               AI.PrincipalInvestigatorId AS PrincipalInvestigatorId,
               AI.ProposalTitle AS ProposalTitle, 
               AI.SponsorName AS PrimarySponsor,
               AI.SecondarySponsor AS SecondarySponsor,
               AI.Department AS Department,
               AI.ChiefCode AS ChiefCode,
               AI.ProposalShortTitle AS ShortProposalTitle,
               AI.ActivityDescription AS Activity,
               AI.SubActivityType AS SubActivity,
               AI.CSCRCaseManager AS CSCRCaseManager,
               ISNULL(AI.CSCRCaseManagerName,'CSCR Case Manager Not Assigned') AS CSCRManagerName,
               AI.ProposalStatus AS ProposalStatus,
               AI.ReceivedDate AS CreatedDate,
               AI.FolderNumber AS ProposalNumber,
               AI.Institution AS Institution,
               AI.SponsorTypeName AS SponsorType,
               AI.ResourceId AS AgrResourceId,
               AI.AgreementId AS AgreemendId
     FROM      dbo.AgrAgreement_Info AI LEFT OUTER JOIN  
               
               -- We need only the latest BudgetYear and BudgetDates for each 
               -- FolderNumber within the search date params.
               dbo.fnGetPeriodBudget(@StartDate, @EndDate) BUDG ON AI.AgreementId = BUDG.AgreementId INNER JOIN
                                                 
               -- Security Issues: Not all Users can see all Agreements!
               #tt_Agreements AUTH ON AI.AgreementId = AUTH.AgreementId
                         
     WHERE     (dbo.fnRemoveTime(AI.ReceivedDate) BETWEEN  @StartDate AND @EndDate) AND
               (@Institution IS NULL OR AI.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Institution))) AND
               (AI.SponsorTypeName = @SponsorType) AND  
               (@ProposalStatus IS NULL OR AI.AgreementStatusId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@ProposalStatus)))               
     ORDER BY AI.PrincipalInvestigator

     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     
     DROP TABLE #tt_Agreements
     
     IF @Error!=0
          RETURN -1
          
     -- Return Success
     RETURN 0
     
END
go

IF OBJECT_ID('dbo.RptIndustryAgreementsReceived') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptIndustryAgreementsReceived >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptIndustryAgreementsReceived >>>'
go

GRANT EXECUTE ON dbo.RptIndustryAgreementsReceived TO InsightReport,Insight
go
